package DBman;

import Integrity.CreditResult;
import Integrity.MunicipalityResult;
import Integrity.Result;
import Integrity.TelcoResult;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
//import java.util.Date;

import scheduler.ScheduledItem;
import scheduler.AuthenticationPair;
import scheduler.IdentifierPair;
import scheduler.TextValuePair;

/**
 * This class is the manager for database access for the APS project.
 * @author Johann Jungbauer
 */
public class DatabaseManager {
    
    private Connection _Connection;
    private String _FileName
            = "C:\\Users\\Administrator\\Documents\\lombozProjects\\WTP-Vikings-APS\\src\\DBman\\database2.properties";
    
    public DatabaseManager() throws IOException, ClassNotFoundException, SQLException
    {
        this.init();
    }
    
    private void init()throws IOException, ClassNotFoundException, SQLException
    {
        Properties props = new Properties();
        FileInputStream in = new FileInputStream(_FileName);
        props.load(in);
        
        String driver = props.getProperty("jdbc.driver");
        String url = props.getProperty("jdbc.url");
        String username = props.getProperty("jdbc.username");
        String password = props.getProperty("jdbc.password");
        Class.forName(driver);
        
        _Connection = DriverManager.getConnection(url,username,password);
    }
    
    public void storeResult(CreditResult result)
    {
        //check error
        // if no error store as normal
        // if error set error var and set to inactive
    }
    
    public void storeResult(MunicipalityResult result)
    {
        //check error
        // if no error store as normal
        // if error set error var and set to inactive
    }
    
    public void storeResult(TelcoResult result)
    {
        //check error
        // if no error store as normal
        // if error set error var and set to inactive
    }
    
    public void storeResult(Result result) throws SQLException
    {
        String usrID = result.get_userID();
        String billingRef = "BS001";
        String errCode = result.get_error_code();
        
        String query = "UPDATE Jobs " +
                "SET Status = 'inactive', LatestResult = ? " +
                "WHERE UserID = ? AND BillingSiteRef = ?";
        PreparedStatement stat = _Connection.prepareStatement(query);
        stat.setString(1, errCode);
        stat.setString(2, usrID);
        stat.setString(3, billingRef);
        stat.executeQuery();
        
        //set job inactive
        // if no error store as normal
        // if error set error var and set to inactive
    }
    
    public boolean storeUser(User user)
    {
        //TODO
        return true;
    }
    
    /**
     * Used to return the password stored in the database for a given user.
     * @param userID The userID of the user you want a password for
     * @return   The string format of the password in the database
     */
    public String retrPassword(String userID) throws SQLException
    {   
        String query = "SELECT Password FROM Users WHERE UserID = ?";
        PreparedStatement stat = _Connection.prepareStatement(query);
        stat.setString(1, userID);
        
        ResultSet result = stat.executeQuery();
        result.next();
        return result.getString("Password");
    }
    
    /**
     * Used to return the list of active scrape jobs.
     * @return   An ArrayList of ScheduledItems
     * @see    scheduler.ScheduledItem
     */
    public ArrayList<ScheduledItem> retrScheduleItems() throws SQLException
    {
        Statement stat = _Connection.createStatement();
        ArrayList<ScheduledItem> schedule = new ArrayList<ScheduledItem>();
        
        ResultSet result = stat.executeQuery("SELECT UserID,BillingSiteRef,LastRun,LastStatementDate,LastStatementTotal FROM Jobs WHERE Status = 'active'");
        
        while (result.next())
        {
            schedule.add(new ScheduledItem(result.getString("UserID"), result.getString("BillingSiteRef"),  
                result.getDate("LastRun"), result.getDate("LastStatementDate"), result.getDouble("LastStatementTotal")));
        }

        return schedule;
    }
    
    /**
     * Used to get a User object of a user in the database.
     * @param userID The userID of the user you want an object for
     * @return   The User object containing User data
     */
    public User retrUser(String userID) throws SQLException
    {
        // basic user info
        String query = "SELECT * FROM Users WHERE UserID = ?";
        PreparedStatement stat = _Connection.prepareStatement(query);
        stat.setString(1, userID);
        
        ResultSet result = stat.executeQuery();
        result.next();
        User usr = new User(result.getString("UserID"),result.getString("Password"),result.getString("Name"));
        
        // add any other set() methods here
        
        // build Job array
        String jobQuery = "SELECT * FROM Jobs WHERE UserID = ?";
        PreparedStatement jobStat = _Connection.prepareStatement(jobQuery);
        jobStat.setString(1, result.getString("UserID"));
        
        ResultSet jobResult = jobStat.executeQuery();
        ArrayList<JobData> temp = new ArrayList<JobData>();

        while(jobResult.next())
        {
            JobData tempJ = new JobData(jobResult.getString("BillingSiteRef"));
            tempJ.setLastRunDate(jobResult.getDate("LastRun"));
            tempJ.setLastStatementDate(jobResult.getDate("LastStatementDate"));
            tempJ.setLastStatementTotal(jobResult.getFloat("LastStatementTotal"));
            tempJ.setLatestResult(jobResult.getString("LatestResult"));
            tempJ.setStatus(jobResult.getString("Status"));
            
            String[] authVals = {jobResult.getString("AuthVal0"),jobResult.getString("AuthVal1"),
                jobResult.getString("AuthVal2"),jobResult.getString("AuthVal3"),jobResult.getString("AuthVal4")};
            String[] identVals = {jobResult.getString("IdentVal0"),jobResult.getString("IdentVal1"),
                jobResult.getString("IdentVal2"),jobResult.getString("IdentVal3"),jobResult.getString("IdentVal4")};
            
            // build auth pairs
            String authQuery = "SELECT Auth0,Auth1,Auth2,Auth3,Auth4 from ADMIN.BILLINGSITES where BillingSiteRef = ?";
            PreparedStatement authStat = _Connection.prepareStatement(authQuery);
            authStat.setString(1, tempJ.getBillingSiteRef());
        
            ResultSet authResult = authStat.executeQuery();
            authResult.next();
            
            AuthenticationPair[] authPairs = new AuthenticationPair[5];
            
            for(int i = 0; i < 5;i++)
            {
                //System.out.println(authResult.getString(i+1) + " == " + authVals[i]);
                AuthenticationPair Atemp = new AuthenticationPair();
                Atemp.setText(authResult.getString(i+1));
                Atemp.setValue(authVals[i]);
                //Atemp.print();
                authPairs[i] = Atemp;
                //authPairs[i].print();
            }
            
            //tempJ.setAuthenticationPairs(authPairs);
            
            // build identfy pairs
            String identQuery = "SELECT ident0,ident1,ident2,ident3,ident4 from ADMIN.BILLINGSITES where BillingSiteRef = ?";
            PreparedStatement identStat = _Connection.prepareStatement(identQuery);
            identStat.setString(1, tempJ.getBillingSiteRef());
        
            ResultSet identResult = identStat.executeQuery();
            identResult.next();
            
            IdentifierPair[] identPairs = new IdentifierPair[5];
            
            for(int i = 0; i < 5;i++)
            {
                //System.out.println(identResult.getString(i+1) + " == " + identVals[i]);
                IdentifierPair Itemp = new IdentifierPair();
                Itemp.setText(identResult.getString(i+1));
                Itemp.setValue(identVals[i]);
                //Atemp.print();
                identPairs[i] = Itemp;
                //authPairs[i].print();
            }
            
            tempJ.setAuthenticationPairs(authPairs);
            tempJ.setIdentifierPairs(identPairs);
            
            temp.add(tempJ);
        }
        
        usr.addJobs(temp);
        
        return usr;
    }
    
    public BillingSiteData retrBillingSite(String billingSiteRef)
    {
        throw new UnsupportedOperationException("Not yet implemented");
        //return new BillingSiteData(billingSiteRef, "bill1.xml", "telco", 10, 10, 10, 10);
    }
    
    /**
     * This method is used to retrieve the type of billing site without having 
     * to create a billing site object.
     * @param billingRef A String of the billing site reference.
     * @return The type of the site in String format.
     */
    public String getBillingSiteType(String billingRef) throws SQLException
    {
        String query = "SELECT Type FROM BillingSites WHERE BillingSiteRef = ?";
        PreparedStatement stat = _Connection.prepareStatement(query);
        stat.setString(1, billingRef);
        
        ResultSet result = stat.executeQuery();
        result.next();
        
        return result.getString("Type");
    }
    
    /**
     * Used to register/add a new user in the database.
     * @param usr This is a User object containing the relevant data.
     */
    public User registerUser(String name, String pw) throws SQLException
    {
        //throw new UnsupportedOperationException("Not yet implemented");
        Statement stat = _Connection.createStatement();
        
        ResultSet result = stat.executeQuery("SELECT COUNT(UserID) FROM Users");
        result.next();
        //System.out.println(result.getInt(1));
        String uid = "UID"+(result.getInt(1)+1);
        System.out.println(uid);
        
        insertUserTable(uid, pw, name);
        
        return retrUser(uid);
    }
    
    private void insertUserTable(String uid, String pw, String name) throws SQLException
    {
        System.out.println("inserting into users");
        String usersInsert = "INSERT INTO Users VALUES (?, ?, ?)";
        PreparedStatement stat = _Connection.prepareStatement(usersInsert);
        stat.setString(1, uid);
        stat.setString(2, pw);
        stat.setString(3, name);
        
        stat.execute();
    }
    
    /**
     * This method is used to update user information in the database.
     * @param usr This is a User object containing the relevant data.
     */
    public void updateUser(User usr) throws SQLException
    {
        // update Users table
        updateUsersTable(usr.getUserID(),usr.getSitePW(),usr.getName());
        
        // update Jobs table
        ArrayList<JobData> jobList = usr.getJobs();
        ArrayList<TextValuePair> updateList = createBillingList(usr.getUserID());
        
        // looping through user jobs and db jobs to catch changes, add/delete/update
        System.out.println(jobList.size());
        for(int i = 0; i < jobList.size();i++)
        {
            //System.out.println(jobList.get(i).getBillingSiteRef());
            boolean found = false;
            for(int x = 0; x < updateList.size();x++)
            {
                if(jobList.get(i).getBillingSiteRef().compareTo(updateList.get(x).getText()) == 0)
                {
                    updateList.get(x).setValue("update");
                    found = true;
                    updateJobTable(usr.getUserID(),jobList.get(i));
                }
            }
            System.out.println(found);
            
            if(!found)
            {
                System.out.println("$$$$$$$$$$$$$$$$$$$$");
                // don't think I need a chunk of this code, except the last line.
                TextValuePair temp = new TextValuePair();
                temp.setText(jobList.get(i).getBillingSiteRef());
                temp.setValue("add");
                updateList.add(temp);
                insertJobIntoDB(usr.getUserID(),jobList.get(i).getBillingSiteRef());
            }
        }
        
        // looping to delete jobs and references
        for(int i = 0; i < updateList.size(); i++)
        {
            if(updateList.get(i).getValue().compareTo("delete") == 0)
            {
                deleteJobFromDB(usr.getUserID(),updateList.get(i).getText());
            }
        }
    }
    
    private void updateJobTable(String uid, JobData job) throws SQLException
    {
        System.out.println("updating");
        IdentifierPair identiPairs[] = new IdentifierPair[5];
        AuthenticationPair authPairs[] = new AuthenticationPair[5];
        authPairs = job.getAuthenticationPairs();
        identiPairs = job.getIdentifierPairs();
                
        String jobUpdate = "UPDATE Jobs SET IdentVal0 = ?,IdentVal1 = ?,IdentVal2 = ?," +
                "IdentVal3 = ?,IdentVal4 = ?," +
                " AuthVal0 = ?, AuthVal1 = ?, AuthVal2 = ?, AuthVal3 = ?," +
                " AuthVal4 = ? WHERE UserID = ? AND BillingSiteRef =?";
        PreparedStatement stat = _Connection.prepareStatement(jobUpdate);
        System.out.println("#"+identiPairs[0].getValue()+"#");
        stat.setString(1, identiPairs[0].getValue());
        stat.setString(2, identiPairs[1].getValue());
        stat.setString(3, identiPairs[2].getValue());
        stat.setString(4, identiPairs[3].getValue());
        stat.setString(5, identiPairs[4].getValue());
        stat.setString(6, authPairs[0].getValue());
        stat.setString(7, authPairs[1].getValue());
        stat.setString(8, authPairs[2].getValue());
        stat.setString(9, authPairs[3].getValue());
        stat.setString(10, authPairs[4].getValue());
        stat.setString(11, uid);
        stat.setString(12, job.getBillingSiteRef());
        
        
        int i = stat.executeUpdate();
    }
    
    private void insertJobIntoDB(String uid, String billRef) throws SQLException
    {
        System.out.println("inserting");
        String jobInsert = "INSERT INTO Jobs VALUES (?,?,'01/01/1990','01/01/1990',0," +
                "'none','update'," +
                "'','','','',''," +
                "'','','','','')";
        PreparedStatement stat = _Connection.prepareStatement(jobInsert);
        stat.setString(1, uid);
        stat.setString(2, billRef);
        stat.execute();
        
        insertIntoCommonTable(uid,billRef);
        String type = getBillingSiteType(billRef);
        if(type.compareTo("telco") == 0)
        {
            insertIntoTelcoTable(uid, billRef);
        }
        else if(type.compareTo("credit") == 0)
        {
            insertIntoCreditTable(uid, billRef);
        }
        else if(type.compareTo("municipality") == 0)
        {
            insertIntoMuncipleTable(uid, billRef);
        }
    }
    
    private void insertIntoCommonTable(String uid, String billRef) throws SQLException
    {
        System.out.println("inserting into common");
        String commonInsert = "INSERT INTO Common VALUES (?, ?," +
                "'', '', '01/01/1990', ''," +
                "'', 0, '01/01/1990', 0, 0, 0," +
                " 0, 0, 0, 0)";
        PreparedStatement stat = _Connection.prepareStatement(commonInsert);
        stat.setString(1, uid);
        stat.setString(2, billRef);
        
        stat.execute();
    }
    
    private void insertIntoTelcoTable(String uid, String billRef) throws SQLException
    {
        String telcoInsert = "INSERT INTO Telco VALUES (?, ?,'', '', 0, 0, 0)";
        PreparedStatement stat = _Connection.prepareStatement(telcoInsert);
        stat.setString(1, uid);
        stat.setString(2, billRef);
        
        stat.execute();
    }
    
    private void insertIntoCreditTable(String uid, String billRef) throws SQLException
    {
        System.out.println("inserting into credit");
        String ccInsert = "INSERT INTO CreditCards VALUES(?, ?,'', '', 0, 0, 0)";
        PreparedStatement stat = _Connection.prepareStatement(ccInsert);
        stat.setString(1, uid);
        stat.setString(2, billRef);
        
        stat.execute();
    }
    
    private void insertIntoMuncipleTable(String uid, String billRef) throws SQLException
    {
        String munInsert = "INSERT INTO Municipality VALUES(?, ?,'', ,'', , , )";
        PreparedStatement stat = _Connection.prepareStatement(munInsert);
        stat.setString(1, uid);
        stat.setString(2, billRef);
        
        stat.execute();
    }
    
    private void deleteJobFromDB(String uid, String billRef) throws SQLException
    {
        String jobDel = "DELETE FROM Jobs WHERE UserID = ? AND BillingSiteRef = ?";
        PreparedStatement stat = _Connection.prepareStatement(jobDel);
        stat.setString(1, uid);
        stat.setString(2, billRef);
        stat.execute();
        
        deleteFromCommonTable(uid,billRef);
        String type = getBillingSiteType(billRef);
        if(type.compareTo("telco") == 0)
        {
            deleteFromTelcoTable(uid, billRef);
        }
        else if(type.compareTo("credit") == 0)
        {
            deleteFromCreditTable(uid, billRef);
        }
        else if(type.compareTo("municipality") == 0)
        {
            deleteFromMuncipleTable(uid, billRef);
        }
    }
    
    private void deleteFromCommonTable(String uid, String billRef) throws SQLException
    {
        String commonDel = "DELETE FROM Common WHERE UserID = ? AND BillingSiteRef = ?";
        PreparedStatement stat = _Connection.prepareStatement(commonDel);
        stat.setString(1, uid);
        stat.setString(2, billRef);
        
        stat.execute();
    }
    
    private void deleteFromTelcoTable(String uid, String billRef) throws SQLException
    {
        String telcoDel = "DELETE FROM Telco WHERE UserID = ? AND BillingSiteRef = ?";
        PreparedStatement stat = _Connection.prepareStatement(telcoDel);
        stat.setString(1, uid);
        stat.setString(2, billRef);
        
        stat.execute();
    }
    
    private void deleteFromCreditTable(String uid, String billRef) throws SQLException
    {
        String ccDel = "DELETE FROM CreditCards WHERE UserID = ? AND BillingSiteRef = ?";
        PreparedStatement stat = _Connection.prepareStatement(ccDel);
        stat.setString(1, uid);
        stat.setString(2, billRef);
        
        stat.execute();
    }
    
    private void deleteFromMuncipleTable(String uid, String billRef) throws SQLException
    {
        String munDel = "DELETE FROM Jobs WHERE UserID = ? AND BillingSiteRef = ?";
        PreparedStatement stat = _Connection.prepareStatement(munDel);
        stat.setString(1, uid);
        stat.setString(2, billRef);
        
        stat.execute();
    }
    
    private ArrayList<TextValuePair> createBillingList(String uid) throws SQLException
    {
        String query = "SELECT BillingSiteRef FROM Jobs WHERE UserID = ?";
        PreparedStatement stat = _Connection.prepareStatement(query);
        stat.setString(1, uid);
        
        ResultSet result = stat.executeQuery();
        
        ArrayList<TextValuePair> pairs = new ArrayList<TextValuePair>();
        
        while(result.next())
        {
            TextValuePair temp = new TextValuePair();
            temp.setText(result.getString("BillingSiteRef"));
            temp.setValue("delete");
            pairs.add(temp);
        }
        
        return pairs;
    }
    
    
    private void updateUsersTable(String uid, String pw, String name) throws SQLException
    {
        String query = "UPDATE Users SET Password = ?, Name = ? WHERE UserID = ?";
        PreparedStatement stat = _Connection.prepareStatement(query);
        stat.setString(1, pw);
        stat.setString(2, name);
        stat.setString(3, uid);
        
        int i = stat.executeUpdate();
    }
    
    public void getUserDataResults(String uid)
    {
        throw new UnsupportedOperationException("Not yet implemented");
    }
            
}
